ITHACK
【MySQL】トリガーの基本操作

【MySQL】トリガーの基本操作

MySQLのトリガーの基本操作について解説します。

検証環境

トリガー

トリガーは“データ操作に起動する処理”です。

データの追加（INSERT）、更新（UPDATE）、削除（DELETE）の操作を行った時に任意の処理を実行することができます。

作成（CREATE TRIGGER）

トリガーの作成はCREATE TRIGGER文を使用します。

基本構文

CREATE TRIGGER トリガー名 [ AFTER | BEFORE ] [ INSERT | UPDATE | DELETE ]
ON テーブル名 FOR EACH ROW
クエリ

[ AFTER | BEFORE ]はトリガーの起動タイミングです。

操作処理の前はBEFORE、後はAFTERを記述します。

[ INSERT | UPDATE | DELETE ]はデータ操作の種類です。

データの追加はINSERT、更新はUPDATE、削除はDELETEを記述します。

また、FOR EACH ROWはトリガーの起動単位です。

FOR EACH ROWはレコード単位でトリガーを起動します。

サンプル

mysql> DESC items;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> DESC items_log;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| content | varchar(100) | YES  |     | NULL    |                |
| dt      | datetime     | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

___ih_hl_start
mysql> CREATE TRIGGER item_insert_before_log
    -> BEFORE INSERT
    -> ON items FOR EACH ROW
    -> INSERT INTO items_log( content, dt )
    -> VALUE( 'INSERT-BEFORE', NOW() );
___ih_hl_end
Query OK, 0 rows affected (0.01 sec)

２０〜２４行目がCREATE TRIGGER文です。

item_insert_before_logトリガーはitemsテーブルのデータ追加時に起動し、items_logテーブルにデータを追加します。

実際にitemsテーブルにデータを追加すると次のような結果になります。

mysql> INSERT INTO items ( name ) VALUE ( 'Apple' );
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM items;
+----+-------+
| id | name  |
+----+-------+
|  1 | Apple |
+----+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM items_log;
+----+---------------+---------------------+
| id | content       | dt                  |
+----+---------------+---------------------+
|  1 | INSERT-BEFORE | 2024-05-13 14:20:59 |
+----+---------------+---------------------+
1 row in set (0.00 sec)

一覧表示（SHOW TRIGGERS）

トリガーの一覧表示はSHOW TRIGGERS文を使用します。

基本構文

SHOW TRIGGERS

サンプル

___ih_hl_start
mysql> SHOW TRIGGERS;
___ih_hl_end
+------------------------+--------+-------+----------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger                | Event  | Table | Statement                                                            | Timing | Created                | sql_mode                                                                                                              | Definer        | character_set_client | collation_connection | Database Collation |
+------------------------+--------+-------+----------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| item_insert_before_log | INSERT | items | INSERT INTO items_log( content, dt )
VALUE( 'INSERT-BEFORE', NOW() ) | BEFORE | 2024-05-13 14:20:50.43 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------------------------+--------+-------+----------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)

情報表示（SHOW CREATE TRIGGER）

トリガーの情報表示はSHOW CREATE TRIGGER文を使用します。

基本構文

SHOW CREATE TRIGGER トリガー名

サンプル

___ih_hl_start
mysql> SHOW CREATE TRIGGER item_insert_before_log;
___ih_hl_end
+------------------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
| Trigger                | sql_mode                                                                                                              | SQL Original Statement                                                                                                                                                        | character_set_client | collation_connection | Database Collation | Created                |
+------------------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
| item_insert_before_log | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER `item_insert_before_log` BEFORE INSERT ON `items` FOR EACH ROW INSERT INTO items_log( content, dt )
VALUE( 'INSERT-BEFORE', NOW() ) | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci | 2024-05-13 14:20:50.43 |
+------------------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
1 row in set (0.00 sec)

削除（DROP TRIGGER）

トリガーの削除はDROP TRIGGER文を使用します。

基本構文

DROP TRIGGER トリガー名

サンプル

mysql> SHOW TRIGGERS;
+------------------------+--------+-------+----------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger                | Event  | Table | Statement                                                            | Timing | Created                | sql_mode                                                                                                              | Definer        | character_set_client | collation_connection | Database Collation |
+------------------------+--------+-------+----------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| item_insert_before_log | INSERT | items | INSERT INTO items_log( content, dt )
VALUE( 'INSERT-BEFORE', NOW() ) | BEFORE | 2024-05-13 14:20:50.43 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------------------------+--------+-------+----------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

___ih_hl_start
mysql> DROP TRIGGER item_insert_before_log;
___ih_hl_end
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TRIGGERS;
Empty set (0.01 sec)

カラムデータの参照（NEW/OLD）

トリガーの処理でデータを参照するにはNEWまたはOLDを使用します。

基本構文

NEW.カラム名
OLD.カラム名

INSERTではNEWでデータを参照します。

UPDATEでは更新前データはOLD、更新後データはNEWです。

DELETEではOLDで値を参照できます。

サンプル

mysql> DESC items;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> DESC items_log;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| content | varchar(100) | YES  |     | NULL    |                |
| dt      | datetime     | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> CREATE TRIGGER item_insert_before_log
    -> BEFORE INSERT
    -> ON items FOR EACH ROW
    -> INSERT INTO items_log( content, dt )
    ___ih_hl_start
    -> VALUE( CONCAT('INSERT-BEFORE: ', NEW.name) , NOW() );
    ___ih_hl_end
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO items ( name ) VALUE ( 'Apple' );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM items;
+----+-------+
| id | name  |
+----+-------+
|  1 | Apple |
+----+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM items_log;
+----+----------------------+---------------------+
| id | content              | dt                  |
+----+----------------------+---------------------+
|  1 | INSERT-BEFORE: Apple | 2024-05-13 14:32:52 |
+----+----------------------+---------------------+
1 row in set (0.00 sec)

２４行目のトリガー作成のクエリ内でNEWを使用しています。

itemsテーブルのデータ追加時に、起動するトリガーですが、その後のSELECT文の結果からNEWによってカラムデータが正常に取得できたことが分かります。

複数クエリ（BEGIN END）

トリガーに複数の処理（クエリ）を設定するにはBEGIN ENDとDELIMITERを使用します。

基本構文

DELIMITER //

CREATE TRIGGER トリガー名 [ AFTER | BEFORE ] [ INSERT | UPDATE | DELETE ]
ON テーブル名 FOR EACH ROW
BEGIN
クエリ1;
クエリ2;
...
END;
//

DELIMITER ;

初めにDELIMITERで終端文字を変更します。

終端文字はクエリの終了を示す文字で、デフォルトではセミコロン（;）です。

複数処理のあるトリガー作成のクエリは、途中でセミコロンを使用するため、クエリが途中終了しないようにDELIMITER //で一時的に終端文字を//に変更します。

その後、トリガー作成クエリを実行しますが、複数処理（クエリ）はBEGINとENDの間に記述し、最後は//で締めます。

最後に終端文字をDELIMITER ;でセミコロンに戻し、トリガー作成が完了です。

サンプル

mysql> DESC items;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> DESC items_log;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| content | varchar(100) | YES  |     | NULL    |                |
| dt      | datetime     | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

___ih_hl_start
mysql> DELIMITER //

mysql> CREATE TRIGGER item_insert_after_log
    -> AFTER INSERT
    -> ON items FOR EACH ROW
    -> BEGIN
    -> INSERT INTO items_log( content, dt )
    -> VALUE( CONCAT('INSERT-AFTER [id] ', NEW.id) , NOW() );
    -> INSERT INTO items_log( content, dt )
    -> VALUE( CONCAT('INSERT-AFTER [name] ', NEW.name) , NOW() );
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
___ih_hl_end

mysql> INSERT INTO items ( name ) VALUE ( 'Apple' );
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM items;
+----+-------+
| id | name  |
+----+-------+
|  1 | Apple |
+----+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM items_log;
+----+---------------------------+---------------------+
| id | content                   | dt                  |
+----+---------------------------+---------------------+
|  1 | INSERT-AFTER [id] 1       | 2024-05-13 15:39:02 |
|  2 | INSERT-AFTER [name] Apple | 2024-05-13 15:39:02 |
+----+---------------------------+---------------------+
2 rows in set (0.00 sec)

２０〜３４行目が複数処理のあるトリガー作成の一連の流れです。

最初と最後にDELIMITERで、トリガー作成が正常に処理されるように終端文字を変更しています。

トリガーはitemsテーブルのデータ追加時に、items_logテーブルにデータを追加する２つ処理を持ちます。

３６行目以降の処理から、１度のデータ追加で２つの処理が実行されたことが分かります。

ER図（IE記法） - データベースの設計図
【RDB】データベースの正規形
関数従属性 - 属性（データ）の関係性
【RDB】関係スキーマとキー - 関係スキーマの表記ルールとキーの種類
【RDB】外部キー - 別カラムを参照するカラム
【RDB】プライマリーキー（主キー） - データを一意に識別するカラム
AtMarkRule
Browser
Command
CSS
Database
Development
DOM
Event
HTML
JavaScript
MarkupLanguage
MySQL
Network
OS
PHP
Programming
Property
Pseudo Class
Pseudo Element
Relational Database
Server
Standard Object
Tag